﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

namespace SMS.dao
{
    public class DataFetcher
    {
        private static readonly DataFetcher INSTANCE = new DataFetcher();
        private readonly String connectionString = "Data Source=KIET-PC\\ANHKIET;Initial Catalog=SMS;Persist Security Info=True;User ID=sa;Password=sa";
        private SqlCommand command;
        private DataTable table = null;
        private DataSet dataSet = null;
        private ArrayList mylist = null;
        private int nConString = 1;

        private DataFetcher() { }

        public static DataFetcher Instance
        {
            get
            {
                return INSTANCE;
            }
        }

        /// <summary>
        /// This method is for settting ConnectionString if a project uses more than two ConnectionString.
        /// The index of the first ConnectionString in web.config file is 1.
        /// </summary>
        /// <param name="nConString"></param>
        public void setConString(int nConString)
        {
            // require a positive number >= 1
            this.nConString = nConString;
        }

        /// <summary>
        /// This method is to choose type of SqlCommand: 1 for SQL statement, 2 for stored procedure
        /// </summary>
        /// <param name="query"></param>
        /// <param name="type"></param>
        public void setCommand(String query, int type)
        {
            command = new SqlCommand();
            command.CommandType = type == 1 ? CommandType.Text : CommandType.StoredProcedure;
            command.CommandText = query;
        }

        /// <summary>
        /// This method is used to add parameter(s) SQL statement or stored procedure
        /// </summary>
        /// <param name="aParam"></param>
        public void addParameter(SqlParameter aParam)
        {
            command.Parameters.Add(aParam);
        }

        /// <summary>
        /// This method is used to excute "SELECT SQL statment"
        /// </summary>
        /// <returns>DataTable</returns>
        public DataTable fetch()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = this.connectionString;
            command.Connection = con;

            try
            {
                if (con.State == ConnectionState.Closed)
                    con.Open();

                table = new DataTable();
                table.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                command.Dispose();
                command.Connection.Dispose();
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Fetch Error: " + ex);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return table;
        }

        /// <summary>
        /// This method is used to excute "SELECT SQL statment"
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet fetchDataSet()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = this.connectionString;;
            command.Connection = con;

            try
            {
                if (con.State == ConnectionState.Closed)
                    con.Open();

                dataSet = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(command);
                da.Fill(dataSet);
                command.Dispose();
                command.Connection.Dispose();
            }
            catch (SqlException ex)
            {
                Console.WriteLine("FetchData Error: " + ex);
            }

            return dataSet;
        }

        /// <summary>
        /// This method is used to excute "INSERT, UPDATE, DELETE SQL statment"
        /// </summary>
        /// <returns>int</returns>
        public int execute()
        {
            //String connectionString = ConfigurationManager.ConnectionStrings[nConString].ConnectionString;
            SqlConnection con = new SqlConnection();
            con.ConnectionString = this.connectionString;
            command.Connection = con;
            int result = -1;

            try
            {
                if (con.State == ConnectionState.Closed)
                    con.Open();

                result = command.ExecuteNonQuery();
                command.Dispose();
                command.Connection.Dispose();
            }
            catch (SqlException ex)
            {
                //HttpContext.Current.Response.Write(ex);
                Console.WriteLine("Execute Error: " + ex);
            }

            return result;
        }

        public int getId()
        {
            //String connectionString = ConfigurationManager.ConnectionStrings[nConString].ConnectionString;
            SqlConnection con = new SqlConnection();
            con.ConnectionString = this.connectionString;
            command.Connection = con;
            int id = -1;

            try
            {
                if (con.State == ConnectionState.Closed)
                    con.Open();

                id = Convert.ToInt32(command.ExecuteScalar());
                command.Dispose();
                command.Connection.Dispose();
            }
            catch (SqlException ex)
            {
                Console.WriteLine("GetId Error: " + ex);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return id;
        }

        public ArrayList getOneRow()
        {
            //String connectionString = ConfigurationManager.ConnectionStrings[nConString].ConnectionString;
            SqlConnection con = new SqlConnection();
            con.ConnectionString = this.connectionString;
            command.Connection = con;

            try
            {
                if (con.State == ConnectionState.Closed)
                    con.Open();

                SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow);
                object[] values = new object[reader.FieldCount];
                mylist = new ArrayList();

                if (reader.FieldCount > 0)
                {
                    while (reader.Read())
                    {
                        reader.GetValues(values);
                    }
                }

                mylist.AddRange(values);
                command.Dispose();
                command.Connection.Dispose();
            }
            catch (SqlException ex)
            {
                Console.WriteLine("GetRow Error: " + ex);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return mylist;
        }
    }
}